<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.codeplex.springsurv.dataaccess.mappers.SurveysMapper">

	<cache />
	
	<resultMap type="com.codeplex.springsurv.Survey" id="surveyMap">
		<id property="i64Id" column="survId" javaType="java.lang.Long" jdbcType="BIGINT" />
		<result property="strName" column="survName" javaType="java.lang.String" jdbcType="VARCHAR" />
	</resultMap>
	
	<resultMap type="com.codeplex.springsurv.Question" id="questionMap">
		<id property="i64Id" column="questId" javaType="java.lang.Long" jdbcType="BIGINT" />
		<result property="strQuestion" column="questText" javaType="java.lang.String" jdbcType="VARCHAR" />
	</resultMap>
	
	<resultMap type="com.codeplex.springsurv.Answer" id="answerMap">
		<id property="i64Id" column="answId" javaType="java.lang.Long" jdbcType="BIGINT" />
		<result property="strAnswer" column="answText" javaType="java.lang.String" jdbcType="VARCHAR" />
	</resultMap>
	
	<select id="getSurveyById" parameterType="list" resultMap="surveyMap">
		SELECT survId, survName FROM surveys WHERE survId=#{id};
	</select>
	
	<select id="getSurvUserNickBySurvId" parameterType="list" resultType="java.lang.String">
		SELECT survUserNick FROM surveys WHERE survId=#{id};
	</select>
	
	<select id="getSurveysListNotFilledByUser" parameterType="list" resultMap="surveyMap">
		SELECT * FROM surveys WHERE survActive=true AND survUserNick NOT IN(#{strNick}) AND survId NOT IN (SELECT us_svSurvId FROM users_surveys WHERE us_svUserNick = #{strNick});
	</select>
	
	<select id="getSurveysListByUserCreator" parameterType="list" resultMap="surveyMap">
		SELECT * FROM surveys WHERE survActive=true AND survUserNick = #{strNick};
	</select>
	
	<select id="getQuestionListBySurvId" parameterType="list" resultMap="questionMap">
		SELECT * FROM questions WHERE questSurvId=#{id};
	</select>
	
	<select id="getAnswerListByQuestId" parameterType="list" resultMap="answerMap" >
		SELECT * FROM answers WHERE answQuestId=#{id};
	</select>
	
	<insert id="insertSurvey">
		INSERT INTO surveys(survId, survName, survActive, survUserNick) VALUES(#{surv.i64Id}, #{surv.strName}, true, #{surv.usrUser.strNick});
	</insert>
	
	<insert id="insertQuestion">
		INSERT INTO questions(questId, questText, questSurvId) VALUES(#{quest.i64Id}, #{quest.strQuestion}, #{survId});
	</insert>
	
	<insert id="insertAnswer">
		INSERT INTO answers(answId, answText, answQuestId) VALUES(#{answ.i64Id},#{answ.strAnswer}, #{questId});
	</insert>
	
	<select id="getLastSurvId" parameterType="list" resultType="java.lang.Long">
		SELECT MAX(survId) FROM surveys;
	</select>
	
	<select id="getLastQuestId" parameterType="list" resultType="java.lang.Long">
		SELECT MAX(questId) FROM questions;
	</select>
	
	<select id="getLastAnswId" parameterType="list" resultType="java.lang.Long">
		SELECT MAX(answId) FROM answers;
	</select>
	
	<update id="closeSurvey">
		UPDATE surveys SET survActive = false WHERE survId = #{surveyId};
	</update>
	
	<insert id="insertFilledSurvey">
		INSERT INTO users_surveys(us_svUserNick, us_svSurvId, us_svAnswId)VALUES(#{usSurvNick}, #{usSurvSurvId}, #{usSurvAnswId});
	</insert>
	
	<select id="getQuantByAnsId" parameterType="list" resultType="java.lang.Integer">
		SELECT COUNT(*) FROM users_surveys WHERE us_svAnswId = #{ansId};
	</select>
	
	<select id="getQuantUsersBySurvId" parameterType="list" resultType="java.lang.Integer">
		SELECT COUNT(DISTINCT us_svUserNick) AS QuantUsers FROM users_surveys WHERE us_svSurvId = #{survId};
	</select>
	
	<select id="getSurvIdByQuestId" parameterType="list" resultType="java.lang.Long">
		SELECT questSurvId FROM questions WHERE questId=#{id};
	</select>
	
</mapper>